﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

using Warehouse.Common.Entities;

namespace Warehouse.DataAccess.SqlClient
{
    public class UserDA : BaseDA<User>, IUserDA
    {
        protected override User getEntity(IDataReader reader)
        {
            return new User()
            {
                UserID = (int)reader["UserID"],
                UserCode = reader["UserCode"].ToString(),
                UserName = reader["UserName"].ToString(),
                Passwd = reader["Passwd"].ToString(),
                IsEnable = reader["IsEnable"].ToString() == "0",
                Description = reader["Description"].ToString(),
            };
        }

        public User Create()
        {
            return new User()
            {
                UserID = 0,
                UserCode = "",
                UserName = "",
                Passwd = "",
                IsEnable = false,
                Description = "",
            };
        }

        public int Add(User toAdd, out int newID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "INSERT INTO Users (UserCode, UserName, Passwd, IsDisabled, Note)"
                      + " VALUES (@UserCode, @UserName, @Passwd, @IsEnable, @Description)"
                      + "\n" + "SELECT @UserID = SCOPE_IDENTITY()";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //var cmd = db.GetStoredProcCommand("pUserCreate") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserCode", toAdd.UserCode);
            cmd.Parameters.AddWithValue("@UserName", toAdd.UserName);
            cmd.Parameters.AddWithValue("@Passwd", toAdd.Passwd);
            cmd.Parameters.AddWithValue("@IsEnable", toAdd.IsEnable);
            cmd.Parameters.AddWithValue("@Description", toAdd.Description);

            SqlParameter outParameter = cmd.Parameters.Add("@UserID", SqlDbType.Int, 0, "UserID");
            outParameter.Direction = ParameterDirection.Output;

            int affectedRows = db.ExecuteNonQuery(cmd);
            newID = (int)cmd.Parameters["@UserID"].Value;
            return affectedRows;
        }

        public int Update(User toUpdate)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "UPDATE Users SET UserCode=@UserCode, UserName=@UserName, Passwd=@Passwd"
                      + "\n" + ", IsEnable=@IsEnable, Description=@Description"
                      + "\n" + "WHERE UserID=@UserID";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //var cmd = db.GetStoredProcCommand("pUserUpdate") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserID", toUpdate.UserID);
            cmd.Parameters.AddWithValue("@UserCode", toUpdate.UserCode);
            cmd.Parameters.AddWithValue("@UserName", toUpdate.UserName);
            cmd.Parameters.AddWithValue("@Passwd", toUpdate.Passwd);
            cmd.Parameters.AddWithValue("@IsEnable", toUpdate.IsEnable);
            cmd.Parameters.AddWithValue("@Description", toUpdate.Description);

            return db.ExecuteNonQuery(cmd);
        }

        public int Delete(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "DELETE FROM Users WHERE UserID=@UserID";
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //var cmd = db.GetStoredProcCommand("pUserDeleteByID") as SqlCommand;
            cmd.Parameters.AddWithValue("@UserID", id);

            return db.ExecuteNonQuery(cmd);
        }

        public int Delete(Expression<Func<User, bool>> where)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var sql = "DELETE FROM Users WHERE " + where.ToString();
            var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            //var cmd = db.GetStoredProcCommand("pUserDeleteByID") as SqlCommand;

            return db.ExecuteNonQuery(cmd);
        }

        public User GetByID(int id)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            //var sql = "SELECT * FROM Users WHERE UserID=@UserID";
            //var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            ////var cmd = db.GetStoredProcCommand("pUserLoadById") as SqlCommand;
            //cmd.Parameters.AddWithValue("@UserID", id);

            //return getSingle(db, cmd);

            return new User()
            {
                UserID = id,
                UserCode = id.ToString(),
                UserName = id.ToString(),
                Passwd = id.ToString(),
                IsEnable = false,
                Description = "",
            };
        }

        public User GetByCode(string userCode)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            //var sql = "SELECT * FROM Users WHERE UserCode=@UserCode";
            //var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            ////var cmd = db.GetStoredProcCommand("pUserLoadByCode") as SqlCommand;
            //cmd.Parameters.AddWithValue("@UserCode", userCode);

            //return getSingle(db, cmd);
            return new User()
            {
                UserID = 0,
                UserCode = userCode,
                UserName = userCode,
                Passwd = userCode,
                IsEnable = false,
                Description = "",
            };
        }

        public IEnumerable<User> GetList(Expression<Func<User, bool>> where)
        {
            //Database db = DatabaseFactory.CreateDatabase();
            //var condition = where.ToString();
            //var sql = "SELECT * FROM Users";
            //if (!string.IsNullOrEmpty(condition))
            //{
            //    sql += " WHERE " + condition;
            //}
            //var cmd = db.GetSqlStringCommand(sql) as SqlCommand;
            ////var cmd = db.GetStoredProcCommand("pUserLoadAll") as SqlCommand;

            //return getMany(db, cmd);

            var list = new List<User>();
            list.Add(
                new User()
                {
                    UserID=1,
                    UserCode="001",
                    UserName="001",
                    IsEnable=true,
                    Passwd="001",
                    DepartmentID=1,
                    UserGroupID=1,
                });
            list.Add(
                new User()
                {
                    UserID=2,
                    UserCode="002",
                    UserName="002",
                    IsEnable=true,
                    Passwd="002",
                    DepartmentID=1,
                    UserGroupID=1,
                });
            list.Add(
                new User()
                {
                    UserID=3,
                    UserCode="003",
                    UserName="003",
                    IsEnable=true,
                    Passwd="003",
                    DepartmentID=1,
                    UserGroupID=1,
                });
            return list;

        }
    }
}
